| ticker | date | ret | roeq | |
|---|---|---|---|---|
| 0 | EWST | 2000-01 | -0.044118 | 0.017047 |
| 1 | SABC | 2000-01 | -0.025641 | 0.027942 |
| 2 | IROQ | 2000-01 | -0.008475 | 0.031816 |
| 3 | DPAC | 2000-01 | -0.097276 | 0.045041 |
| 4 | SCTT | 2000-01 | -0.099338 | 0.061219 |
Kerry Back
from sqlalchemy import create_engine
import pymssql
import pandas as pd
server = "mssql-82792-0.cloudclusters.net:16272"
username = "user"
password = "" # paste password between quote marks
database = "ghz"
string = "mssql+pymssql://" + username + ":" + password + "@" + server + "/" + database
conn = create_engine(string).connect()data = pd.read_sql(
"""
select ticker, date, ret, roeq
from data
order by date
""",
conn
)
data = data.dropna()
data.head()| ticker | date | ret | roeq | |
|---|---|---|---|---|
| 0 | EWST | 2000-01 | -0.044118 | 0.017047 |
| 1 | SABC | 2000-01 | -0.025641 | 0.027942 |
| 2 | IROQ | 2000-01 | -0.008475 | 0.031816 |
| 3 | DPAC | 2000-01 | -0.097276 | 0.045041 |
| 4 | SCTT | 2000-01 | -0.099338 | 0.061219 |
data["quintile"] = data.groupby("date").roeq.transform(
lambda x: pd.qcut(x, 5, labels=range(1, 6))
)
data.head()| ticker | date | ret | roeq | decile | |
|---|---|---|---|---|---|
| 0 | EWST | 2000-01 | -0.044118 | 0.017047 | 3 |
| 1 | SABC | 2000-01 | -0.025641 | 0.027942 | 3 |
| 2 | IROQ | 2000-01 | -0.008475 | 0.031816 | 4 |
| 3 | DPAC | 2000-01 | -0.097276 | 0.045041 | 4 |
| 4 | SCTT | 2000-01 | -0.099338 | 0.061219 | 5 |
| decile | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| date | |||||
| 2000-01 | 0.199432 | 0.075519 | 0.016835 | -0.014594 | -0.003020 |
| 2000-02 | 0.366746 | 0.141581 | 0.043943 | 0.043550 | 0.063504 |
| 2000-03 | -0.065864 | 0.014373 | 0.018757 | 0.036923 | 0.054467 |
| 2000-04 | -0.217721 | -0.073392 | -0.037092 | -0.018193 | -0.047450 |
| 2000-05 | -0.134255 | -0.056660 | -0.020230 | -0.025020 | -0.044011 |
decile
1 0.010083
2 0.011746
3 0.012197
4 0.012553
5 0.012682
dtype: float64